﻿/************************************************************************
 * 
 *   文件描述： 业务逻辑提交类
 *   创建信息： 王喆    2007-12-17
 *   修改信息： 
 * 
 ************************************************************************/

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;

namespace LiteFrame
{
    public class DataHelper
    {
        SQLiteConnection objConnection;     // 数据库连接对象
        SQLiteTransaction objTransaction;   // 数据库事务对象
        string strConnection;               // 数据库连接字符串

        /// <summary>
        /// 初始化DataHelper对象
        /// </summary>
        /// <param name="connString">连接字符串</param>
        public DataHelper(string connString)
        {
            this.strConnection = connString;
        }

        /// <summary>
        /// 执行SQL语句并返回DataSet
        /// </summary>
        public DataSet ExecSqlQuery(SQLiteCommand cmdObj)
        {
            DataSet dsRet = new DataSet();
            SQLiteDataAdapter sda = null;
            try
            {
                objConnection = new SQLiteConnection(strConnection);
                cmdObj.Connection = objConnection;
                sda = new SQLiteDataAdapter(cmdObj);
                objConnection.Open();
                objTransaction = objConnection.BeginTransaction();
                sda.Fill(dsRet);
                objTransaction.Commit();
            }
            catch (Exception err)
            {
                objTransaction.Rollback();
                dsRet = null;
                throw new Exception("操作数据库时出现异常：" + err.ToString());
            }
            finally
            {
                objConnection.Close();
                cmdObj.Dispose();
                sda.Dispose();
            }
            return dsRet;
        }

        /// <summary>
        /// 执行SQL语句并返回影响条数
        /// </summary>
        /// <param name="cmdObj"></param>
        /// <returns></returns>
        public int ExecNonQuery(SQLiteCommand cmdObj)
        {
            int ret;
            try
            {
                objConnection = new SQLiteConnection(strConnection);
                cmdObj.Connection = objConnection;
                objConnection.Open();
                objTransaction = objConnection.BeginTransaction();
                ret = cmdObj.ExecuteNonQuery();
                objTransaction.Commit();
            }
            catch (Exception err)
            {
                objTransaction.Rollback();
                ret = 0;
                throw new Exception("操作数据库时出现异常：" + err.ToString());
            }
            finally
            {
                objConnection.Close();
                cmdObj.Dispose();
            }
            return ret;
        }

        /// <summary>
        /// 执行SQL语句并返回[0][0]的值。
        /// </summary>
        /// <param name="cmdObj"></param>
        /// <returns></returns>
        public string ExecuteScalar(SQLiteCommand cmdObj)
        {
            string retString;
            try
            {
                objConnection = new SQLiteConnection(strConnection);
                cmdObj.Connection = objConnection;
                objConnection.Open();
                objTransaction = objConnection.BeginTransaction();
                retString = cmdObj.ExecuteScalar().ToString();
                objTransaction.Commit();
            }
            catch (Exception err)
            {
                objTransaction.Rollback();
                retString = null;
                throw new Exception("操作数据库时出现异常：" + err.ToString());
            }
            finally
            {
                objConnection.Close();
                cmdObj.Dispose();
            }
            return retString;
        }

        /// <summary>
        /// 根据指定路径创建数据库
        /// </summary>
        /// <param name="DBFile">数据库路径</param>
        /// <returns></returns>
        public int CreateDB(string DBFile)
        {
            // 检查是否存在库文件
            if (System.IO.File.Exists(DBFile))
            {
                return 0;  //已存在该文件无需创建。
            }
            try
            {
                SQLiteConnection.CreateFile(DBFile);
                return 1;
            }
            catch (SQLiteException err)
            {
                // 创建失败，记录错误信息。
                //string ErrMsg = err.ToString();
                throw new SQLiteException((int)err.ErrorCode ,"创建数据库文件失败：" + err.Message);
            }
            return -1;
        }


        /*  原来的ExecSqlQuery等函数
        SQLiteCommand cmd;
        SQLiteConnection conn;
        SQLiteTransaction tran;
        /// <summary>
        /// 执行SQL语句并返回DataSet
        /// </summary>
        /// <param name="connStr">数据库连接字符串</param>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns></returns>
        public DataSet ExecSqlQuery(string connStr, string sqlStr)
        {
            DataSet dsRet = new DataSet();
            SQLiteDataAdapter sda = null;
            try
            {
                conn = new SQLiteConnection(connStr);
                cmd = new SQLiteCommand(sqlStr, conn);
                sda = new SQLiteDataAdapter(cmd);
                conn.Open();
                tran = conn.BeginTransaction();
                sda.Fill(dsRet);
                tran.Commit();
            }
            catch (Exception err)
            {
                tran.Rollback();
                dsRet = null;
                throw new Exception("操作数据库时出现异常：" + err.ToString());
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
                sda.Dispose();
            }
            return dsRet;
        }

        /// <summary>
        /// 执行SQL语句并返回
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        public DbDataReader ExecSqlReader(string connStr, string sqlStr)
        {
            SQLiteDataReader reader;
            try
            {
                conn = new SQLiteConnection(connStr);
                cmd = new SQLiteCommand(sqlStr, conn);
                conn.Open();
                tran = conn.BeginTransaction();
                reader = cmd.ExecuteReader();
                tran.Commit();
            }
            catch (Exception err)
            {
                tran.Rollback();
                reader = null;
                throw new Exception("操作数据库时出现异常：" + err.ToString());
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
            }
            return (DbDataReader)reader;
        }

        public int ExecNonQuery(string connStr, string sqlStr)
        {
            int ret;
            try
            {
                conn = new SQLiteConnection(connStr);
                cmd = new SQLiteCommand(sqlStr, conn);
                conn.Open();
                tran = conn.BeginTransaction();
                ret = cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception err)
            {
                tran.Rollback();
                ret = 0;
                throw new Exception("操作数据库时出现异常：" + err.ToString());
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
            }
            return ret;
        }

        public int ExecNonQuery(string connStr, string sqlStr, DbParameter[] paras)
        {
            int ret;
            try
            {
                conn = new SQLiteConnection(connStr);
                cmd = new SQLiteCommand(sqlStr, conn);
                foreach (DbParameter para in paras)
                {
                    cmd.Parameters.Add(para);
                }
                conn.Open();
                tran = conn.BeginTransaction();
                ret = cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception err)
            {
                tran.Rollback();
                ret = 0;
                throw new Exception("操作数据库时出现异常：" + err.ToString());
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
            }
            return ret;
       }
         */
    }
}
